#include <exception>
#include <fstream>
#include <time.h>
#include <stdlib.h>
#include <stdio.h>
#include "DbOperation.h"
#include "../select/MultiClient.h"
#include <arpa/inet.h>
#include "../CommFormat.h"


static void show_mysql_version(mysqlpp::Connection& con)
{
	cout << "MySQL version: " << con.client_version() << endl;
}

void send2db (struct in6_addr& vr_addr6, string& buff)
{
	char tmp_str[46];
	ofstream db_log;
	char log_file_path[30], h_m_s[10];
	time_t nt = time(NULL);
	string loc_ipv6_str, sql;
	inet_ntop(AF_INET6, (void*)&(vr_addr6), tmp_str, sizeof(tmp_str));
	loc_ipv6_str = tmp_str;
	//cout << "in file " << __FILE__ << " line " << __LINE__ << endl;

	try
	{
	while (buff.size() >= 1)
	{
		//cout << "in file " << __FILE__ << " line " << __LINE__ << endl;
		const int type = buff[0];
		if (ECHO_MSG == type)
		{
			//将回射保活报文截取后继续循环拆包
			buff = buff.substr(1);
			//cout << "将回射保活报文截取后继续循环拆包" << endl;
		}
		else if (VLINK_STAT_INFO == type)
		{
			//cout << "in file " << __FILE__ << " line " << __LINE__ << endl;
			const int arr_len = buff[1];
			const int vlink_num = buff[2];
			//cout << "VLINK_STAT_INFO was recv." << endl;
			//cout << "the array length is : " << arr_len << endl;
			if (0 < arr_len)
			{
				struct VlinkStateInfo tmp_arr[arr_len];
				const int stru_len = sizeof(struct VlinkStateInfo);
				memset(&tmp_arr, 0, stru_len*arr_len);
				try
				{
					//将buff中的内容按结构体循环拷贝到临时数组中
					for (int i = 0; i < arr_len; i++)
					{
						buff.copy((char*)(tmp_arr+i), stru_len, 3+(i*stru_len));
					}
				}
				catch (exception& er)
				{
					cout << "in file " << __FILE__ << " line " << __LINE__ << er.what() << endl;
				}
				buff = buff.substr(3+stru_len*arr_len);
				Connection conn("vrwebmonitor", "127.0.0.1", "root", "bwu929as");
				Query query(conn.query());
				string num_of_vlink, vlink_id, status, go_hops, come_hops, rrt, opp_ipv6_str, loc_ipv4_str, opp_ipv4_str, interval, ntime;
				sprintf(tmp_str, "%d", vlink_num);
				num_of_vlink = tmp_str;
				sql = "update vrlist set numofvlink=" + num_of_vlink + " where vraddr=\"" + loc_ipv6_str + "\";";
				//cout << sql << endl;
				query.use(sql);
				for (int i = 0; i < arr_len; i++)
				{
					sql.clear();
					sprintf(tmp_str, "%d", (tmp_arr+i)->vlink_no);
					vlink_id = tmp_str;
					sprintf(tmp_str, "%d", (tmp_arr+i)->status);
					status = tmp_str;
					sprintf(tmp_str, "%d", (tmp_arr+i)->req_hop_num);
					go_hops = tmp_str;
					sprintf(tmp_str, "%d", (tmp_arr+i)->res_hop_num);
					come_hops = tmp_str;
					(tmp_arr+i)->rrt = ntohl((tmp_arr+i)->rrt);
					sprintf(tmp_str, "%d", (int)(tmp_arr+i)->rrt);
					rrt = tmp_str;
					inet_ntop(AF_INET6, (void*)&((tmp_arr+i)->peer_addr), tmp_str, sizeof(tmp_str));
					opp_ipv6_str = tmp_str;
					inet_ntop(AF_INET, (void*)&((tmp_arr+i)->local_vlink_addr), tmp_str, sizeof(tmp_str));
					loc_ipv4_str = tmp_str;
					inet_ntop(AF_INET, (void*)&((tmp_arr+i)->peer_vlink_addr), tmp_str, sizeof(tmp_str));
					opp_ipv4_str = tmp_str;
					(tmp_arr+i)->hello_interval = ntohs((tmp_arr+i)->hello_interval);
					sprintf(tmp_str, "%d", (tmp_arr+i)->hello_interval);
					interval = tmp_str;
					sprintf(tmp_str, "%d", (int)nt);
					ntime = tmp_str;

					sql = "select * from vrlinkstate where vraddr = \"" + loc_ipv6_str + "\" and locipv4 = \"" + loc_ipv4_str + "\";";
					StoreQueryResult res = query.store(sql);
					if (res.num_rows() > 0)
					{
						sql.clear();
						sql += "update vrlinkstate set ";
						sql += "oppipv4 =      \"" + opp_ipv4_str + "\", ";
						sql += "oppipv6 =      \"" + opp_ipv6_str + "\", ";
						sql += "vlinkid =      \"" + vlink_id     + "\", ";
						sql += "linkstate =    \"" + status       + "\", ";
						sql += "gohops =       \"" + go_hops      + "\", ";
						sql += "comehops =     \"" + come_hops    + "\", ";
						sql += "rtt =          \"" + rrt          + "\", ";
						sql += "sendinterval = \"" + interval     + "\", ";
						sql += "time =         \"" + ntime        + "\"  ";
						sql += "where vraddr = \"" + loc_ipv6_str + "\"  ";
						sql += "and locipv4 =  \"" + loc_ipv4_str + "\"; ";
						//cout << sql << endl;
						query.use(sql);
					}
					else
					{
						sql.clear();
						sql += "insert into vrlinkstate";
						sql += "(vraddr, locipv4, oppipv4, oppipv6, vlinkid, linkstate, gohops, comehops, rtt, sendinterval, time) values (";
						sql += "\"" + loc_ipv6_str + "\", ";
						sql += "\"" + loc_ipv4_str + "\", ";
						sql += "\"" + opp_ipv4_str + "\", ";
						sql += "\"" + opp_ipv6_str + "\", ";
						sql += "\"" + vlink_id     + "\", ";
						sql += "\"" + status       + "\", ";
						sql += "\"" + go_hops      + "\", ";
						sql += "\"" + come_hops    + "\", ";
						sql += "\"" + rrt          + "\", ";
						sql += "\"" + interval     + "\", ";
						sql += "\"" + ntime        + "\");";
						query.use(sql);
					}

					sql.clear();
					sql += "insert into vrlinkstatelog";// + tabsuffix();
					sql += " (vraddr, locipv4, oppipv4, oppipv6, vlinkid, linkstate, gohops, comehops, rtt, sendinterval, time) values (";
					sql += "\"" + loc_ipv6_str + "\", ";
					sql += "\"" + loc_ipv4_str + "\", ";
					sql += "\"" + opp_ipv4_str + "\", ";
					sql += "\"" + opp_ipv6_str + "\", ";
					sql += "\"" + vlink_id     + "\", ";
					sql += "\"" + status       + "\", ";
					sql += "\"" + go_hops      + "\", ";
					sql += "\"" + come_hops    + "\", ";
					sql += "\"" + rrt          + "\", ";
					sql += "\"" + interval     + "\", ";
					sql += "\"" + ntime        + "\");";
					try
					{
						query.use(sql);
					}
					catch (const mysqlpp::Exception& er)
					{
						record_to_logfile(db_log, er.what(), sql, __LINE__, __FUNCTION__, __FILE__);
						string ctsql, tablename;
						tablename = "vrlinkstatelog" + tabsuffix();
						//建立新表的SQL语句
						ctsql += "create table " + tablename;
						ctsql += " (id smallint(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,	\
							  vraddr VARCHAR(40) NOT NULL,	\
							  locipv4 VARCHAR(15) NOT NULL,	\
							  oppipv4 VARCHAR(15) NOT NULL,	\
							  oppipv6 VARCHAR(40) NOT NULL,	\
							  vlinkid smallint(5) NOT NULL,	\
							  linkstate tinyint(1) NOT NULL,	\
							  gohops tinyint(3) NOT NULL,	\
							  comehops tinyint(3) NOT NULL,	\
							  rtt smallint(5) NOT NULL,		\
							  sendinterval smallint(5) NOT NULL,\
							  time int(11) NOT NULL,		\
							  INDEX tindex (time)		\
							  )ENGINE=MyISAM DEFAULT CHARSET=latin1;";
						//cout << ctsql << endl;
						try
						{
							query.use(ctsql);
						}
						catch (const mysqlpp::Exception& er)
						{
							record_to_logfile(db_log, er.what(), ctsql, __LINE__, __FUNCTION__, __FILE__);
						}
						//新表建立成功后，再次插入数据
						query.use(sql);
					}
				}
				conn.disconnect();
			}
		}
		else if (VLINK_PATH_INFO == type)
		{
			sprintf(tmp_str, "%d", buff[1]);
			string vlink_id = tmp_str;
			
			//cout << "VLINK_PATH_INFO was recv." << endl;
			int arr_len = buff[2];
			//cout << "the array length is : " << arr_len << endl;
			if (0 < arr_len)
			{
				struct VlinkIpv6Trace tmp_arr[arr_len];
				int stru_len = sizeof(struct VlinkIpv6Trace);
				memset(&tmp_arr, 0, stru_len*arr_len);
				try 
				{
					for (int i = 0; i < arr_len; i++)
					{
						buff.copy((char*)(tmp_arr+i), stru_len, 3+(i*stru_len));
					}
				}
				catch (exception& er)
				{
					cout << "in file " << __FILE__ << " line " << __LINE__ << er.what() << endl;
				}
				buff = buff.substr(3+stru_len*arr_len);
				Connection conn("vrwebmonitor", "127.0.0.1", "root", "bwu929as");
				Query query(conn.query());
				string sql, nod_ipv6_str, loc_ipv4_str;


				//cout << "Vlink id is : " << vlink_id << endl;
				sql += "select locipv4 from vrlinkstate ";
				sql += "where vraddr = \"" + loc_ipv6_str + "\" and vlinkid = \"" + vlink_id + "\";";
				//sql += " order by time desc limit 1;";
				cout << sql << endl;
				//if (UseQueryResult res = query.use(sql))
				if (StoreQueryResult res = query.store(sql))
				{
					//if (Row row = res.fetch_row())
					cout << "row number is : " << res.num_rows() << endl;
					if (res.num_rows())
					{
						//row["locipv4"].to_string(loc_ipv4_str);
						loc_ipv4_str = res[0]["locipv4"].c_str();
						//cout << "Local ipv4 addr is : " << res[0]["locipv4"] << endl;
						cout << "Local ipv4 addr is : " << loc_ipv4_str << endl;
					}
					else
					{
						cout << "no such vlink in db." << endl;
					}
					if (conn.errnum()) 
					{
						form_path_time(log_file_path, h_m_s);
						db_log.open(log_file_path, ios::app);
						db_log << "Error received in fetching a row: " << conn.error() << " [" << h_m_s << "] ";
						db_log << "[file:" << __FILE__ << "] [func:" << __FUNCTION__ << "] [line:" << __LINE__ << "]" << endl;
					}
				}
				else
				{
					form_path_time(log_file_path, h_m_s);
					db_log.open(log_file_path, ios::app);
					db_log << "Failed to get stock item: " << query.error() << " [" << h_m_s << "] ";
					db_log << "[file:" << __FILE__ << "] [func:" << __FUNCTION__ << "] [line:" << __LINE__ << "]" << endl;
				}
				sql.clear();
				sql = "delete from path where vraddr=\"" + loc_ipv6_str + "\" and firstipv4=\"" + loc_ipv4_str + "\";";
				query.use(sql);
				for (int i = 0; i < arr_len; i++)
				{
					sql.clear();
					inet_ntop(AF_INET6, (void*)&((tmp_arr+i)->addr6), tmp_str, sizeof(tmp_str));
					nod_ipv6_str = tmp_str;

					sql += "insert into path ";
					sql += "(vraddr, firstipv4, nodeipaddr) values (";
					sql += "'" + loc_ipv6_str + "', ";
					sql += "'" + loc_ipv4_str + "', ";
					sql += "'" + nod_ipv6_str + "');";
					cout << sql << endl;
					query.use(sql);
				}
				conn.disconnect();
			}
		}
		else
		{
			cout << "in file " << __FILE__ << " line " << __LINE__ << " no type was macthed." << endl;
			break;
		}
	}//while end
	}//try end
	catch (const BadQuery& er)
	{
		record_to_logfile(db_log, er.what(), sql, __LINE__, __FUNCTION__, __FILE__);
	}
	catch (const Exception& er)
	{
		record_to_logfile(db_log, er.what(), sql, __LINE__, __FUNCTION__, __FILE__);
	}
	catch (exception& er)
	{
		cout << "in file " << __FILE__ << " line " << __LINE__ << er.what() << endl;
	}
}

/*
	int type = buff[0];
	char tmp_str[46];
	ofstream db_log;
	char log_file_path[30], h_m_s[10];
	time_t nt = time(NULL);

	//vr_addr6.__in6_u.__u6_addr16[0] = 0xFF05;
	//vr_addr6.__in6_u.__u6_addr16[7] = 0xb9;
	string loc_ipv6_str, sql;
	inet_ntop(AF_INET6, (void*)&(vr_addr6), tmp_str, sizeof(tmp_str));
	loc_ipv6_str = tmp_str;

	try
	{
		if (VLINK_STAT_INFO == type)
		{
			cout << "VLINK_STAT_INFO was recv." << endl;
			int arr_len = buff[1];
			cout << "the array length is : " << arr_len << endl;
			int vlink_num = buff[2];
			if (0 < arr_len)
			{
				struct VlinkStateInfo tmp_arr[arr_len];
				int stru_len = sizeof(struct VlinkStateInfo);
				//cout << "The length of VlinkStateInfo is : " << stru_len << endl;
				memset(&tmp_arr, 0, stru_len*arr_len);
				try 
				{
					for (int i = 0; i < arr_len; i++)
					{
						buff.copy((char*)(tmp_arr+i), stru_len, 3+(i*stru_len));
					}
				}
				catch (exception& e)
				{
					cout << e.what() << endl;
				}
				Connection conn("vrwebmonitor", "127.0.0.1", "root", "bwu929as");
				Query query(conn.query());
				string num_of_vlink, vlink_id, status, go_hops, come_hops, rrt, opp_ipv6_str, loc_ipv4_str, opp_ipv4_str, interval, ntime;
				sprintf(tmp_str, "%d", vlink_num);
				num_of_vlink = tmp_str;
				sql = "update vrlist set numofvlink=" + num_of_vlink + " where vraddr=\"" + loc_ipv6_str + "\";";
				//cout << sql << endl;
				query.use(sql);
				for (int i = 0; i < arr_len; i++)
				{
					sql.clear();
					sprintf(tmp_str, "%d", (tmp_arr+i)->vlink_no);
					vlink_id = tmp_str;
					//cout << "vlink_id: " << vlink_id << endl;
					sprintf(tmp_str, "%d", (tmp_arr+i)->status);
					status = tmp_str;
					//cout << "status: " << status << endl;
					sprintf(tmp_str, "%d", (tmp_arr+i)->req_hop_num);
					go_hops = tmp_str;
					//cout << "go_hops: " << go_hops << endl;
					sprintf(tmp_str, "%d", (tmp_arr+i)->res_hop_num);
					come_hops = tmp_str;
					//cout << "come_hops: " << come_hops << endl;
					(tmp_arr+i)->rrt = ntohl((tmp_arr+i)->rrt);
					sprintf(tmp_str, "%d", (int)(tmp_arr+i)->rrt);
					rrt = tmp_str;
					//cout << "rrt: " << rrt << endl;
					inet_ntop(AF_INET6, (void*)&((tmp_arr+i)->peer_addr), tmp_str, sizeof(tmp_str));
					opp_ipv6_str = tmp_str;
					//cout << "opp_ipv6_str: " << opp_ipv6_str << endl;
					inet_ntop(AF_INET, (void*)&((tmp_arr+i)->local_vlink_addr), tmp_str, sizeof(tmp_str));
					loc_ipv4_str = tmp_str;
					//cout << "loc_ipv4_str: " << loc_ipv4_str << endl;
					inet_ntop(AF_INET, (void*)&((tmp_arr+i)->peer_vlink_addr), tmp_str, sizeof(tmp_str));
					opp_ipv4_str = tmp_str;
					//cout << "opp_ipv4_str: " << opp_ipv4_str << endl;
					(tmp_arr+i)->hello_interval = ntohs((tmp_arr+i)->hello_interval);
					sprintf(tmp_str, "%d", (tmp_arr+i)->hello_interval);
					interval = tmp_str;
					//cout << "interval: " << interval << endl;
					sprintf(tmp_str, "%d", (int)nt);
					ntime = tmp_str;
					//cout << "ntime: " << ntime << endl;

					sql = "select * from vrlinkstate where vraddr = \"" + loc_ipv6_str + "\" and locipv4 = \"" + loc_ipv4_str + "\";";
					StoreQueryResult res = query.store(sql);
					//aff_row = 0xffffffff & (~aff_row);
					if (res.num_rows() > 0)
					{
						sql.clear();
						sql += "update vrlinkstate set ";
						sql += "oppipv4 =      \"" + opp_ipv4_str + "\", ";
						sql += "oppipv6 =      \"" + opp_ipv6_str + "\", ";
						sql += "vlinkid =      \"" + vlink_id     + "\", ";
						sql += "linkstate =    \"" + status       + "\", ";
						sql += "gohops =       \"" + go_hops      + "\", ";
						sql += "comehops =     \"" + come_hops    + "\", ";
						sql += "rtt =          \"" + rrt          + "\", ";
						sql += "sendinterval = \"" + interval     + "\", ";
						sql += "time =         \"" + ntime        + "\"  ";
						sql += "where vraddr = \"" + loc_ipv6_str + "\"  ";
						sql += "and locipv4 =  \"" + loc_ipv4_str + "\"; ";
						//cout << sql << endl;
						query.use(sql);
					}
					else 
					{
						sql.clear();
						sql += "insert into vrlinkstate";
						sql += "(vraddr, locipv4, oppipv4, oppipv6, vlinkid, linkstate, gohops, comehops, rtt, sendinterval, time) values (";
						sql += "\"" + loc_ipv6_str + "\", ";
						sql += "\"" + loc_ipv4_str + "\", ";
						sql += "\"" + opp_ipv4_str + "\", ";
						sql += "\"" + opp_ipv6_str + "\", ";
						sql += "\"" + vlink_id     + "\", ";
						sql += "\"" + status       + "\", ";
						sql += "\"" + go_hops      + "\", ";
						sql += "\"" + come_hops    + "\", ";
						sql += "\"" + rrt          + "\", ";
						sql += "\"" + interval     + "\", ";
						sql += "\"" + ntime        + "\");";
						query.use(sql);
					}
					
					sql.clear();
					sql += "insert into vrlinkstatelog";// + tabsuffix();
					sql += " (vraddr, locipv4, oppipv4, oppipv6, vlinkid, linkstate, gohops, comehops, rtt, sendinterval, time) values (";
					sql += "\"" + loc_ipv6_str + "\", ";
					sql += "\"" + loc_ipv4_str + "\", ";
					sql += "\"" + opp_ipv4_str + "\", ";
					sql += "\"" + opp_ipv6_str + "\", ";
					sql += "\"" + vlink_id     + "\", ";
					sql += "\"" + status       + "\", ";
					sql += "\"" + go_hops      + "\", ";
					sql += "\"" + come_hops    + "\", ";
					sql += "\"" + rrt          + "\", ";
					sql += "\"" + interval     + "\", ";
					sql += "\"" + ntime        + "\");";
					try
					{
						query.use(sql);
					}
					catch (const mysqlpp::Exception& er)
					{
						record_to_logfile(db_log, er.what(), sql, __LINE__, __FUNCTION__, __FILE__);
						string ctsql, tablename;
						tablename = "vrlinkstatelog" + tabsuffix();
						ctsql += "create table " + tablename;
						ctsql += " (id smallint(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,	\
						      vraddr VARCHAR(40) NOT NULL,	\
						      locipv4 VARCHAR(15) NOT NULL,	\
						      oppipv4 VARCHAR(15) NOT NULL,	\
						      oppipv6 VARCHAR(40) NOT NULL,	\
						      vlinkid smallint(5) NOT NULL,	\
						      linkstate tinyint(1) NOT NULL,	\
						      gohops tinyint(3) NOT NULL,	\
						      comehops tinyint(3) NOT NULL,	\
						      rtt smallint(5) NOT NULL,		\
						      sendinterval smallint(5) NOT NULL,\
						      time int(11) NOT NULL,		\
						      INDEX tindex (time)		\
						      )ENGINE=MyISAM DEFAULT CHARSET=latin1;";
						//cout << ctsql << endl;
						try
						{
							query.use(ctsql);
						}
						catch (const mysqlpp::Exception& er)
						{
							record_to_logfile(db_log, er.what(), ctsql, __LINE__, __FUNCTION__, __FILE__);
						}
						query.use(sql);
					}
				}
				conn.disconnect();
			}
		}
		else if (VLINK_PATH_INFO == type)
		{
			cout << "VLINK_PATH_INFO was recv." << endl;
			int arr_len = buff[2];
			cout << "the array length is : " << arr_len << endl;
			if (0 < arr_len)
			{
				struct VlinkIpv6Trace tmp_arr[arr_len];
				int stru_len = sizeof(struct VlinkIpv6Trace);
				memset(&tmp_arr, 0, stru_len*arr_len);
				try 
				{
					for (int i = 0; i < arr_len; i++)
					{
						buff.copy((char*)(tmp_arr+i), stru_len, 3+(i*stru_len));
					}
				}
				catch (exception& e)
				{
					cout << e.what() << endl;
				}
				Connection conn("vrwebmonitor", "127.0.0.1", "root", "bwu929as");
				Query query(conn.query());
				string sql, nod_ipv6_str, loc_ipv4_str;

				sprintf(tmp_str, "%d", buff[1]);
				string vlink_id = tmp_str;
				sql += "select locipv4 from vrlinkstate ";
				sql += "where vraddr = \"" + loc_ipv6_str + "\" and vlinkid = \"" + vlink_id + "\";";
				//sql += " order by time desc limit 1;";
				cout << sql << endl;
				if (UseQueryResult res = query.use(sql))
				{
					while (Row row = res.fetch_row())
					{
						row["locipv4"].to_string(loc_ipv4_str);
						cout << "Local ipv4 addr is : " << loc_ipv4_str << endl;
					}
					if (conn.errnum()) 
					{
						form_path_time(log_file_path, h_m_s);
						db_log.open(log_file_path, ios::app);
						db_log << "Error received in fetching a row: " << conn.error() << " [" << h_m_s << "] ";
						db_log << "[file:" << __FILE__ << "] [func:" << __FUNCTION__ << "] [line:" << __LINE__ << "]" << endl;
					}
				}
				else
				{
					form_path_time(log_file_path, h_m_s);
					db_log.open(log_file_path, ios::app);
					db_log << "Failed to get stock item: " << query.error() << " [" << h_m_s << "] ";
					db_log << "[file:" << __FILE__ << "] [func:" << __FUNCTION__ << "] [line:" << __LINE__ << "]" << endl;
				}
				sql.clear();
				sql = "delete from path where vraddr=\"" + loc_ipv6_str + "\" and firstipv4=\"" + loc_ipv4_str + "\";";
				query.use(sql);
				for (int i = 0; i < arr_len; i++)
				{
					sql.clear();
					inet_ntop(AF_INET6, (void*)&((tmp_arr+i)->addr6), tmp_str, sizeof(tmp_str));
					nod_ipv6_str = tmp_str;

					sql += "insert into path ";
					sql += "(vraddr, firstipv4, nodeipaddr) values (";
					sql += "'" + loc_ipv6_str + "', ";
					sql += "'" + loc_ipv4_str + "', ";
					sql += "'" + nod_ipv6_str + "');";
					cout << sql << endl;
					query.use(sql);
				}
				conn.disconnect();
			}
		}
		else
		{
			cout << "in logfile: unrecognizable data came from VR.";
		}
	}
	catch (const BadQuery& er)
	{

		record_to_logfile(db_log, er.what(), sql, __LINE__, __FUNCTION__, __FILE__);
	}
	catch (const Exception& er)
	{
		record_to_logfile(db_log, er.what(), sql, __LINE__, __FUNCTION__, __FILE__);
	}
	catch (exception& e)
	{
		cerr << e.what() << endl;
	}


*/

inline const string tabsuffix ()
{
	char tmp[5];
	struct tm* nowtime;
	time_t nt;

	nt = time(NULL);
	nowtime = localtime(&nt);
	strftime(tmp, sizeof(tmp), "%y%m", nowtime);

	string suf("_");
	suf.append(tmp, 4);

	return suf;
}
